import os
import pymysql
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='root', database='calender', charset='utf8')
cur = conn.cursor()
def getsql_normal(): ## 还缺少循环task的读取，往后增加即可
    # tasks={}
    sql="select * from normal "
    # sql = "insert into normal(id,this_year,this_month,this_day,color,start_time,end_time,is_all_day,title,details) VALUES \
    #     (111,'2022','12','21','green','12:00','17:05',False,'晨曦','fytfug'),\
    #     (121,'2022','11','21','blue','11:00','17:23',False,'chenchen','vytuy') ;  "
    cur.execute(sql)
    conn.commit()
    test1 = cur.fetchall()
    year1 = {}
    for i in test1:
        task = {}
        task["id"]=i[0]
        task["color"]=i[4]
        task["start_time"]=i[5]
        task["end_time"]=i[6]
        if i[7]==0:
            task["is_all_day"]=False
        else:
            task["is_all_day"]=True
        task["title"]=i[8]
        if i[9] == "&nbsp;":
            task["details"]=""
            pass
        else:
            task["details"]=i[9]
        task["fileurl"]=i[10]
        if year1.__contains__(i[1]):
            pass
        else:
            year1[i[1]]={}
        if year1[i[1]].__contains__(i[2]):
            pass
        else:
            year1[i[1]][i[2]]={}
        if year1[i[1]][i[2]].__contains__(i[3]):
            year1[i[1]][i[2]][i[3]].append(task)
        else:
            year1[i[1]][i[2]][i[3]]=[]
            year1[i[1]][i[2]][i[3]].append(task)
        year1[i[1]][i[2]]=year1[i[1]][i[2]]
        # year1[i[1]]=year1[i[1]]
    # tasks["normal"]=year1
    # print (year1)
    return year1

def writesql_normal(id,this_year,this_month,this_day,color,start_time,end_time,is_all_day,title,details,fileurl):
    sql = "insert into normal(id,this_year,this_month,this_day,color,start_time,end_time,is_all_day,title,details,fileurl) VALUES \
        ({0},'{1}','{2}','{3}','{4}','{5}','{6}',{7},'{8}','{9}','{10}')\
            ;".format(id,this_year,this_month,this_day,color,start_time,end_time,is_all_day,title,details,fileurl) 
    cur.execute(sql)
    conn.commit()

def writesql_repetition(id,color,start_time,end_time,is_all_day,title,details,repetition_type,repetition_subtype,repetition_value,fileurl):
    sql="insert into repetition\
        (id,color,start_time,end_time,is_all_day,title,details,repetition_type,repetition_subtype,repetition_value,fileurl) VALUES \
    ({0},'{1}','{2}','{3}',{4},'{5}','{6}','{7}','{8}',{9},'{10}')\
        ;".format(id,color,start_time,end_time,is_all_day,title,details,repetition_type,repetition_subtype,repetition_value,fileurl)
    cur.execute(sql)
    conn.commit()

# def changesql(table,this_year='',this_month='',this_day='',color='',start_time='',end_time='',is_all_day='',title='',details=''):
# def changesql(table,**kwargs): ## 需要判断是否是循环日期
#     # sql = "UPDATE {table} SET id,this_year,this_month,this_day,color,start_time,end_time,is_all_day,title,details \
#     #     WHERE id = {id}".format(table=table,id=id)
#     id=-1
#     sql="UPDATE {table} SET ".format(table=table)
#     skip_first=0
#     # 生成类似于下面的
#     # UPDATE Person SET Address = 'Zhongshan 23', City = 'Nanjing' WHERE LastName = 'Wilson'
#     for i,j in kwargs.items():
#         if skip_first!=0:
#             sql=sql+", "
#         if skip_first!=0 and skip_first!=7:
#             sql=sql+str(i)
#             sql=sql+"="
#             sql=sql+"\""+str(j)+"\""
#             skip_first=skip_first+1
#         else:
#             sql=sql+str(i)
#             sql=sql+"="
#             sql=sql+str(j)
#             skip_first=skip_first+1
#         if i == "id":
#             id=i
#     sql=sql+" where id = {id};".format(id=id)
#     cur.execute(sql)
#     conn.commit()
#     # sql=sql+

def getsql_repetition():
    tasks_list=[]
    # tasks={}
    sql="select * from repetition;"
    cur.execute(sql)
    conn.commit()
    test1 = cur.fetchall()
    for i in test1:
        tasks={}
        tasks['id']=i[0]
        tasks['color']=i[1]
        tasks['start_time']=i[2]
        tasks['end_time']=i[3]
        if i[4]==0:
            tasks["is_all_day"]=False
        else:
            tasks["is_all_day"]=True
        # tasks['is_all_day']=i[4]
        tasks['title']=i[5]
        tasks['details']=i[6]
        tasks['repetition_type']=i[7]
        tasks['repetition_subtype']=i[8]
        tasks['repetition_value']=i[9]
        tasks["fileurl"]=i[10]
        tasks_list.append(tasks)
    # print(tasks_list)
    return tasks_list
    pass

def getsql_all():
    all={}
    tasks={}
    tasks["normal"]=getsql_normal()
    tasks["repetition"]=getsql_repetition()
    tasks["hidden_repetition"]={}
    all["tasks"]=tasks
    all["users"]=["a_username"]
    all["name"]="Personal"
    # print(all)
    return all
    pass

def deletesql(table:str,
        # calendar_id: str,
        # year_str: str,
        # month_str: str,
        # day_str: str,
        task_id: int
):
    sql1="SELECT fileurl FROM {0} WHERE id={1};".format(table,task_id)
    # print(22)
    # print(sql1)
    cur.execute(sql1)
    conn.commit()
    fileurl = cur.fetchall()
    # print(fileurl)
    # print(fileurl[0][0])
    # print("down ")
    # print(777)
    # os.remove("flask_calendar/"+fileurl[0][0])
    #  不删除tmp了，防止原地tp
    sql2="DELETE FROM {0} WHERE id={1};".format(table,task_id)
    cur.execute(sql2)
    conn.commit()
    pass


def sql_query(keyword):
    # (1668131420, '2022', '12', '21', '#F2981A', '00:00', '00:00', 1, '朱琳生日', '&nbsp;', '')
    sql1= "select * from normal where details like '%{keyword}%' or title like '%{keyword}%'".format(keyword=keyword)
    cur.execute(sql1)
    result1_list=[]
    result1=cur.fetchall()
    for i in result1:
        if i:
            dateone=i[1]+"-"+i[2]+"-"+i[3]
            titleone=i[8]
            detailone=i[9]
            one=dateone+" "+titleone+" "+detailone
            result1_list.append(one)
    sql2= "select * from repetition where details like '%{keyword}%' or title like '%{keyword}%'".format(keyword=keyword)
    cur.execute(sql2)
    result2_list=[]
    result2=cur.fetchall()
    for i in result2:
        if i:
            # dateone=i[1]+"-"+i[2]+"-"+i[3]
            titleone=i[5]
            detailone=i[6]
            one=titleone+" "+detailone
            result2_list.append(one)
    result=result1_list+result2_list
    print(result)
    return result
 


# if __name__ == "__main__":
#     # getsql_repetition()
#     writesql_normal(232355,'2022','12','21','green','12:00','17:05',False,'晨曦','fytfug')
#     writesql_repetition(1244123,"black","00:00","00:00",True,"tttt","fghjg","m","w",1)
#     # getsql_repetition()

    

